﻿Imports Control_Vuelos_BE
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class V_ValijaADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    ReadOnly cmd As New SqlCommand()

    Public Function pr_AgregarValija(ByVal p_V_ValijaBE As V_ValijaBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Using oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
                Const sInstruccion As String = "INSERT INTO dbo.V_Valija(CodViaje,TipoValija,RemitenteValija,DestinatarioValija,EstadoValija,ObsValija) VALUES (@CodViaje,@TipoValija,@RemitenteValija,@DestinatarioValija,@EstadoValija,@ObsValija)"
                Dim cmd As New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                cmd.Parameters.AddWithValue("@CodViaje", p_V_ValijaBE.CodViaje)
                cmd.Parameters.AddWithValue("@TipoValija", p_V_ValijaBE.TipoValija)
                cmd.Parameters.AddWithValue("@RemitenteValija", p_V_ValijaBE.RemitenteValija)
                cmd.Parameters.AddWithValue("@DestinatarioValija", p_V_ValijaBE.DestinatorioValija)
                cmd.Parameters.AddWithValue("@EstadoValija", p_V_ValijaBE.EstadoValija)
                cmd.Parameters.AddWithValue("@ObsValija", p_V_ValijaBE.ObsValija)
                Try
                    cmd.ExecuteNonQuery()
                    oTran.Commit()
                    Return True
                Catch myException As Exception
                    MsgBox("V_ValijaADO (pr_AgregarValija) --> " & myException.Message, MsgBoxStyle.Critical)
                    oTran.Rollback()
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_EliminarValija(ByVal p_V_ValijaBE As V_ValijaBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
            Dim sInstruccion As String = "DELETE FROM dbo.V_Valija  WHERE " & _
                " CodViaje  = @CodViaje  AND CorrelativoValija = @CorrelativoValija "
            Dim cmd As New SqlCommand(sInstruccion, cn)
            cmd.Transaction = oTran
            cmd.Parameters.AddWithValue("@CodViaje", p_V_ValijaBE.CodViaje)
            cmd.Parameters.AddWithValue("@CorrelativoValija", p_V_ValijaBE.CorrelativoValija)
            Try
                cmd.ExecuteNonQuery()
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("V_ValijaADO (pr_EliminarValija) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_GetValija() As DataTable
        Using dts As New DataSet()
            Dim ada As SqlDataAdapter
            Try
                cmd.Connection = cnx
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "sp_ListadeValija"
                ada = New SqlDataAdapter(cmd)
                ada.Fill(dts, "V_Valija")
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
            Return dts.Tables("V_Valija")
        End Using
    End Function

    Public Function pr_ListarValijas(ByVal p_CodViaje As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Lista_Valijas", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodViaje.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Valijas")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("V_ValijaADO (ListarValijas) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarValijasImpresion(ByVal p_CodViaje As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Lista_Valijas_Impresion", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodViaje", SqlDbType.Char, 12)
                    cmd.Parameters("@p_CodViaje").Value = p_CodViaje.Trim
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Valijas")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("V_ValijaADO (ListarValijasImpresion) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ModificarValija(ByVal p_V_ValijaBE As V_ValijaBE) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Using oTran As SqlTransaction = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim sInstruccion As String = "UPDATE dbo.V_Valija  SET TipoValija = @TipoValija, RemitenteValija = @RemitenteValija,DestinatarioValija = @DestinatarioValija,ObsValija = @ObsValija WHERE " & " CodViaje  = @CodViaje  AND CorrelativoValija = @CorrelativoValija "
                Dim cmd As New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                cmd.Parameters.AddWithValue("@CodViaje", p_V_ValijaBE.CodViaje)
                cmd.Parameters.AddWithValue("@CorrelativoValija", p_V_ValijaBE.CorrelativoValija)
                cmd.Parameters.AddWithValue("@TipoValija", p_V_ValijaBE.TipoValija)
                cmd.Parameters.AddWithValue("@RemitenteValija", p_V_ValijaBE.RemitenteValija)
                cmd.Parameters.AddWithValue("@DestinatarioValija", p_V_ValijaBE.DestinatorioValija)
                cmd.Parameters.AddWithValue("@EstadoValija", p_V_ValijaBE.EstadoValija)
                cmd.Parameters.AddWithValue("@ObsValija", p_V_ValijaBE.ObsValija)
                Try
                    cmd.ExecuteNonQuery()
                    oTran.Commit()
                    Return True
                Catch myException As Exception
                    MsgBox("V_ValijaADO (pr_ModificarValija) --> " & myException.Message, MsgBoxStyle.Critical)
                    oTran.Rollback()
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_Valijas() As DataSet
        Try
            Dim sql As String = "SELECT C_Manifiesto.FchViaje,C_Manifiesto.CodLugarOrigen,C_Manifiesto.CodLugarDestino,M_Aeropuerto.NomAeropuerto,C_Manifiesto.CodEmpresa,C_Manifiesto.ObsCabeceraManifiesto,C_Manifiesto.EstadoManifiesto,C_Manifiesto.FlgValija,C_Manifiesto.HraViaje,M_ResponsableVuelo.nomResponsable AS Contacto,C_Manifiesto.CodViaje,M_Lugar.NomLugar AS DesOrigen, M_Lugar1.NomLugar as DesDestino, M_EmpresaTransporte.NomEmpresa " & _
                    " FROM dbo.C_Manifiesto INNER JOIN dbo.M_Lugar ON C_Manifiesto.CodLugarOrigen = M_Lugar.CodLugar " & _
                    " INNER JOIN dbo.M_Lugar M_Lugar1 ON C_Manifiesto.CodLugarDestino = M_Lugar1.CodLugar " & _
                    " INNER JOIN dbo.M_EmpresaTransporte ON C_Manifiesto.CodEmpresa = M_EmpresaTransporte.CodEmpresa " & _
                    " INNER JOIN M_Aeropuerto ON M_Aeropuerto.codOrigen=C_Manifiesto.CodLugarOrigen AND M_Aeropuerto.nroCorrelativo=C_Manifiesto.CodAeropuerto  " & _
                    " INNER JOIN M_ResponsableVuelo ON M_ResponsableVuelo.codOrigen=C_Manifiesto.CodLugarOrigen AND M_ResponsableVuelo.NroCorrelativo=C_Manifiesto.CodResponsable  "
            '                    " WHERE  C_Manifiesto.CodViaje IN (SELECT V_Valija.CodViaje FROM V_Valija) "
            Const sql1 As String = " Select V_Valija.CodViaje,V_Valija.TipoValija,V_Valija.RemitenteValija,V_Valija.DestinatarioValija,V_Valija.EstadoValija,V_Valija.ObsValija  FROM dbo.V_Valija "
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim dataSet As New DataSet()
            Using cn
                cn.Open()
                Using oleDBAdapter1 As New OleDbDataAdapter(sql, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                    Using oleDBAdapter2 As New OleDbDataAdapter(sql1, "Provider=SQLOLEDB;" & MiConexion.GetCnx)
                        oleDBAdapter1.Fill(dataSet, "Valijas")
                        oleDBAdapter2.Fill(dataSet, "Detalle")
                    End Using
                End Using
                dataSet.Relations.Add("Detalle", dataSet.Tables("Valijas").Columns("CodViaje"), dataSet.Tables("Detalle").Columns("CodViaje"))
                Return dataSet
            End Using
        Catch myException As Exception
            MsgBox("V_ValijaADO (pr_Valijas) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

End Class
